<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use app\index\controller\Login;
use think\Db;
use app\index\service\ExcelService;

class ArchiveBuildingTable extends BaseTable
{
    protected static $table = 'ARCHIVE_BUILDING';

    static public function table(){
        $sql = "(
        select 
        b.ARCHIVE_YEARMONTH
        ,b.BUILDING_NO
        ,to_char(b.INPUT_DATE,'yyyy-mm-dd') INPUT_DATE
        ,b.CHECK_TYPE_ID
        ,b.ASSET_TYPE_ID
        ,d1.CNAME94 ASSET_TYPE
        ,cast(b.BUILDING_NAME as varchar2(75)) BUILDING_NAME
        ,b.BUILDING_UNIT
        ,b.BUILDING_SIZE
        ,b.CONSTRU_ID
        ,d2.CONSTRU
        ,b.BUILDING_MEASUREMENT
        ,b.OFFICE_SIZE
        ,b.MEETING_SIZE
        ,b.CAR_SIZE
        ,b.CANTEEN_SIZE
        ,b.DISTRIBUTION_SIZE
        ,b.LAB_SIZE
        ,b.USE_SIZE
        ,b.UNDER_SIZE
        ,b.ACCOUNT_CERT_NO
        ,b.PROPERTYRIGHT_ID
        ,d3.PROPERTYRIGHT
        ,b.OWNER_CERT
        ,b.OWNER_CERT_NO
        ,to_char(b.CERT_GET_DATE,'yyyy-mm-dd') CERT_GET_DATE
        ,b.BUILDING_FEE
        ,b.AVG_FEE
        ,b.FINANCE_GET_FEE
        ,b.BUSINESS_GET_FEE
        ,b.BUSINESS_BUDGET_GET_FEE
        ,b.OTHER_FEE
        ,b.OTHER_FINANCE_FEE
        ,b.GETTYPE_ID
        ,d4.GETTYPE
        ,to_char(b.GET_DATE,'yyyy-mm-dd') GET_DATE
        ,b.SELF_SIZE
        ,b.SELF_FEE
        ,b.BORROW_SIZE
        ,b.BORROW_FEE
        ,b.RENTAL_SIZE
        ,b.RENTAL_FEE
        ,b.INVESTMENT_SIZE
        ,b.INVESTMENT_FEE
        ,b.GUARANTEE_SIZE
        ,b.GUARANTEE_FEE
        ,b.ELSE_SIZE
        ,b.ELSE_FEE
        ,b.PURCHASE_ID
        ,d5.PURCHASE
        ,b.STATUS_ID
        ,d6.STATUS
        ,b.BORROW_DEPART
        ,b.RENTAL_DEPART
        ,b.DEPRECIATE_STATUS_ID
        ,b.DEPRECIATE_WAY
        ,b.USE_MONTH
        ,b.DEPRECIATED_MONTH
        ,b.DEPRECIATE_PERCENTAGE
        ,b.REDUCE_PRE
        ,b.BUILDING_AREA
        ,b.SELF_DEPART_USE_PERIOD
        ,b.COLLEGE_ID
        ,d7.COLLEGE
        ,b.REMARK
        ,b.USER_ID
        ,d8.NAME USER_NAME
        ,to_char(b.OPER_DATETIME,'yyyy-mm-dd') OPER_DATETIME
        ,b.VALUETYPE_ID
        ,d9.valuetype
        from app_asset.archive_building b   
        left join app_asset.ASSET_DICT_TYPE94TO2010 d1 on d1.ccode94=b.asset_type_id
        left join app_asset.ASSET_DICT_CONSTRU d2 on d2.CONSTRU_ID=b.CONSTRU_ID
        left join app_asset.ASSET_DICT_PROPERTYRIGHT d3 on d3.PROPERTYRIGHT_ID=b.PROPERTYRIGHT_ID
        left join app_asset.ASSET_DICT_GETTYPE d4 on d4.GETTYPE_ID=b.GETTYPE_ID
        left join app_asset.ASSET_DICT_PURCHASE d5 on d5.PURCHASE_ID=b.PURCHASE_ID
        left join app_asset.ASSET_DICT_STATUS d6 on d6.STATUS_ID=b.STATUS_ID
        left join app_asset.DICT_COLLEGE d7 on d7.COLLEGE_ID=b.COLLEGE_ID
        left join app_asset.EMPLOYEE d8 on d8.STAFF_ID=b.USER_ID
        left join app_asset.ASSET_DICT_VALUETYPE d9 on d9.valuetype_id=b.VALUETYPE_ID
        )";
        return Db::table($sql);
    }

    static public function insert($data){
        $yearmonth = $data['ARCHIVE_YEARMONTH'];
        $buildingNo = $data['BUILDING_NO'];
        self::toDate($data,'GET_DATE');
        self::toDate($data,'COMPLETION_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'INPUT_TIME');

        $old = Db::table(static::$table)->where(['ARCHIVE_YEARMONTH'=>$yearmonth,'BUILDING_NO'=>$buildingNo])->find();
        if(empty($old)){
            return Db::table(static::$table)->insert($data);
        }
        else{
            return Db::table(static::$table)
                ->where(['ARCHIVE_YEARMONTH'=>$yearmonth,'BUILDING_NO'=>$buildingNo])
                ->update($data);
        }
    }

}